using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Options;
using CoreManager.Models.Entities;
using CoreManager.Repository.Configuration;
using CoreManager.Models.Enums;

namespace CoreManager.Repository.DbContext
{
    /// <summary>
    /// Entity Framework Core数据库上下文
    /// </summary>
    public class CoreManagerDbContext : Microsoft.EntityFrameworkCore.DbContext
    {
        private readonly IConfiguration _configuration;
        private readonly ILogger<CoreManagerDbContext> _logger;
        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly SeedDataOptions _seedDataOptions;

        public CoreManagerDbContext(DbContextOptions<CoreManagerDbContext> options, 
            IConfiguration configuration, 
            ILogger<CoreManagerDbContext> logger, 
            IHttpContextAccessor httpContextAccessor) : base(options)
        {
            _configuration = configuration;
            _logger = logger;
            _httpContextAccessor = httpContextAccessor;
            
            // 绑定种子数据配置
            _seedDataOptions = new SeedDataOptions();
            _configuration.GetSection(SeedDataOptions.SectionName).Bind(_seedDataOptions);
        }

        /// <summary>
        /// 用户表
        /// </summary>
        public DbSet<User> Users { get; set; }

        /// <summary>
        /// 角色表
        /// </summary>
        public DbSet<Role> Roles { get; set; }

        /// <summary>
        /// 用户角色关联表
        /// </summary>
        public DbSet<UserRole> UserRoles { get; set; }

        /// <summary>
        /// 菜单表
        /// </summary>
        public DbSet<Menu> Menus { get; set; }

        /// <summary>
        /// 角色菜单关联表
        /// </summary>
        public DbSet<RoleMenu> RoleMenus { get; set; }

        /// <summary>
        /// 配置模型
        /// </summary>
        /// <param name="modelBuilder"></param>
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            // 配置软删除全局查询过滤器
            modelBuilder.Entity<User>().HasQueryFilter(e => !e.IsDeleted);
            modelBuilder.Entity<Role>().HasQueryFilter(e => !e.IsDeleted);
            modelBuilder.Entity<UserRole>().HasQueryFilter(e => !e.IsDeleted);
            modelBuilder.Entity<Menu>().HasQueryFilter(e => !e.IsDeleted);
            modelBuilder.Entity<RoleMenu>().HasQueryFilter(e => !e.IsDeleted);

            // 配置实体关系
            modelBuilder.Entity<UserRole>(entity =>
            {
                entity.HasIndex(e => new { e.UserId, e.RoleId }).IsUnique();
            });

            // 配置角色菜单关联实体
            modelBuilder.Entity<RoleMenu>(entity =>
            {
                entity.HasIndex(e => new { e.RoleId, e.MenuId }).IsUnique();
            });

            // 配置菜单实体
            modelBuilder.Entity<Menu>(entity =>
            {
                // 配置菜单Key的唯一索引
                entity.HasIndex(e => e.Key).IsUnique();
                
                // 配置自引用关系
                entity.HasOne(e => e.Parent)
                      .WithMany(e => e.Children)
                      .HasForeignKey(e => e.ParentId)
                      .OnDelete(DeleteBehavior.Restrict);
            });

            // 种子数据
            //SeedData(modelBuilder);
        }

        /// <summary>
        /// 配置日志记录
        /// </summary>
        /// <param name="optionsBuilder"></param>
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);

            // 配置SQL日志 - 只在开发环境显示
            var environment = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT");
            var enableSqlLog = _configuration?.GetValue<bool>("EntityFramework:EnableSqlLog", false) ?? false || 
                              environment == "Development";

            if (enableSqlLog)
            {
                optionsBuilder.LogTo(sql =>
                {
                    // 过滤掉不重要的系统查询
                    if (IsSystemQuery(sql))
                        return;

                    // 获取当前请求的 RequestId
                    var requestId = _httpContextAccessor?.HttpContext?.Items["RequestId"]?.ToString();
                    if (string.IsNullOrEmpty(requestId))
                    {
                        requestId = "system";
                    }

                    // 解析并替换参数到SQL中
                    var finalSql = ParseAndReplaceParameters(sql);

                    if (!string.IsNullOrEmpty(finalSql) && IsCompleteSql(finalSql))
                    {
                        _logger?.LogInformation("[请求ID:{RequestId}] 执行SQL → {Sql}", requestId, finalSql);
                    }
                }, LogLevel.Information);
            }
        }

        /// <summary>
        /// 提取实际的SQL语句，去除EF Core的调试信息
        /// </summary>
        /// <param name="rawSql">原始SQL日志</param>
        /// <returns>清理后的SQL语句</returns>
        private static string ExtractSqlStatement(string rawSql)
        {
            if (string.IsNullOrWhiteSpace(rawSql))
                return string.Empty;

            // 查找SQL语句的开始位置
            var lines = rawSql.Split('\n', StringSplitOptions.RemoveEmptyEntries);
            
            foreach (var line in lines)
            {
                var trimmedLine = line.Trim();
                
                // 跳过EF Core的调试信息行
                if (trimmedLine.StartsWith("info:") || 
                    trimmedLine.StartsWith("Executed DbCommand") || 
                    trimmedLine.Contains("RelationalEventId.CommandExecuted") ||
                    trimmedLine.Contains("Microsoft.EntityFrameworkCore.Database.Command") ||
                    trimmedLine.StartsWith("[Parameters") ||
                    string.IsNullOrWhiteSpace(trimmedLine))
                    continue;

                // 找到实际的SQL语句
                if (IsActualSqlStatement(trimmedLine))
                {
                    // 将多行SQL合并为一行，并清理多余空格
                    var sqlBuilder = new System.Text.StringBuilder();
                    var foundSql = false;
                    
                    for (int i = Array.IndexOf(lines, line); i < lines.Length; i++)
                    {
                        var currentLine = lines[i].Trim();
                        
                        // 跳过调试信息
                        if (currentLine.StartsWith("info:") || 
                            currentLine.StartsWith("Executed DbCommand") || 
                            currentLine.Contains("RelationalEventId.CommandExecuted") ||
                            currentLine.Contains("Microsoft.EntityFrameworkCore.Database.Command") ||
                            currentLine.StartsWith("[Parameters"))
                            continue;
                            
                        if (!string.IsNullOrWhiteSpace(currentLine))
                        {
                            if (sqlBuilder.Length > 0)
                                sqlBuilder.Append(' ');
                            sqlBuilder.Append(currentLine);
                            foundSql = true;
                        }
                    }
                    
                    if (foundSql)
                    {
                        // 清理多余的空格并返回
                        return System.Text.RegularExpressions.Regex.Replace(sqlBuilder.ToString(), @"\s+", " ").Trim();
                    }
                }
            }

            return string.Empty;
        }

        /// <summary>
        /// 判断是否为实际的SQL语句
        /// </summary>
        /// <param name="line">文本行</param>
        /// <returns>是否为SQL语句</returns>
        private static bool IsActualSqlStatement(string line)
        {
            if (string.IsNullOrWhiteSpace(line))
                return false;

            var upperLine = line.ToUpper().Trim();

            // 常见的SQL语句开头
            return upperLine.StartsWith("SELECT") ||
                    upperLine.StartsWith("INSERT") ||
                    upperLine.StartsWith("UPDATE") ||
                    upperLine.StartsWith("DELETE") ||
                    upperLine.StartsWith("CREATE") ||
                    upperLine.StartsWith("ALTER") ||
                    upperLine.StartsWith("DROP") ||
                    upperLine.StartsWith("TRUNCATE") ||
                    upperLine.StartsWith("WITH") ||
                    upperLine.StartsWith("EXEC") ||
                    upperLine.StartsWith("CALL");
        }

        /// <summary>
        /// 判断SQL是否完整（包含FROM或WHERE或完整的语句）
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>是否完整</returns>
        private static bool IsCompleteSql(string sql)
        {
            if (string.IsNullOrWhiteSpace(sql))
                return false;

            var upperSql = sql.ToUpper();

            // 如果包含FROM或WHERE，认为是完整的SELECT
            if (upperSql.Contains(" FROM ") || upperSql.Contains(" WHERE "))
                return true;

            // 对于非SELECT语句，如果不包含参数占位符，认为是完整的
            if (!upperSql.Contains("@__"))
                return true;

            // 如果是简单的COUNT(*)等，认为是完整的
            if (upperSql.Contains("COUNT(*)") || upperSql.Contains("COUNT(1)"))
                return true;

            return false;
        }

        /// <summary>
        /// 解析并替换SQL中的参数，确保返回单行SQL以避免日志换行过多
        /// </summary>
        /// <param name="rawSql">原始SQL日志</param>
        /// <returns>替换参数后的完整单行SQL</returns>
        private static string ParseAndReplaceParameters(string rawSql)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(rawSql))
                    return string.Empty;

                // 提取参数字符串
                var parameters = ExtractParameters(rawSql);

                // 提取和清理SQL行
                var sqlLines = ExtractSqlLines(rawSql);

                // 构建单行SQL
                var cleanSql = BuildSingleLineSql(sqlLines);

                // 替换参数
                if (!string.IsNullOrEmpty(parameters))
                {
                    cleanSql = ReplaceParametersInSql(cleanSql, parameters);
                }
                return cleanSql;

                // 确保最终SQL为单行，移除任何剩余的换行符
                //return cleanSql.Replace("\n", " ").Replace("\r", " ").Trim();
            }
            catch (Exception ex)
            {
                // 记录错误并返回原始SQL的简化版本
                Console.WriteLine($"SQL参数解析失败: {ex.Message}");
                return rawSql?.Replace("\n", " ").Replace("\r", " ").Trim() ?? string.Empty;
            }
        }

        /// <summary>
        /// 从原始SQL日志中提取参数字符串
        /// </summary>
        private static string ExtractParameters(string rawSql)
        {
            if (string.IsNullOrEmpty(rawSql))
                return string.Empty;

            var lines = rawSql.Split('\n', StringSplitOptions.RemoveEmptyEntries);

            foreach (var line in lines)
            {
                var trimmedLine = line.Trim();
                if (trimmedLine.Contains("[Parameters="))
                {
                    int paramStart = trimmedLine.IndexOf("[Parameters=");
                    if (paramStart >= 0)
                    {
                        int bracketStart = trimmedLine.IndexOf('[', paramStart + 11);
                        if (bracketStart >= 0)
                        {
                            int bracketEnd = trimmedLine.IndexOf(']', bracketStart);
                            if (bracketEnd > bracketStart)
                            {
                                return trimmedLine.Substring(bracketStart + 1, bracketEnd - bracketStart - 1);
                            }
                        }
                    }
                }
            }

            return string.Empty;
        }

        /// <summary>
        /// 从原始SQL日志中提取SQL语句行，过滤掉调试信息
        /// </summary>
        private static List<string> ExtractSqlLines(string rawSql)
        {
            var sqlLines = new List<string>();
            if (string.IsNullOrEmpty(rawSql))
                return sqlLines;

            var lines = rawSql.Split('\n', StringSplitOptions.RemoveEmptyEntries);

             sqlLines = lines
                        .Select(line => line.Trim())
                        .Where(trimmedLine => !string.IsNullOrWhiteSpace(trimmedLine) &&
                                              !trimmedLine.StartsWith("info:") &&
                                              !trimmedLine.StartsWith("Executed DbCommand") &&
                                              !trimmedLine.Contains("RelationalEventId.CommandExecuted") &&
                                              !trimmedLine.Contains("Microsoft.EntityFrameworkCore.Database.Command") &&
                                              !trimmedLine.StartsWith("CommandType=") &&
                                              !trimmedLine.StartsWith("CommandTimeout=") &&
                                              !trimmedLine.Contains("[Parameters="))
                        .ToList();


            return sqlLines;
        }

        /// <summary>
        /// 将SQL行构建为单行字符串，清理多余空格
        /// </summary>
        private static string BuildSingleLineSql(System.Collections.Generic.List<string> sqlLines)
        {
            if (sqlLines == null || sqlLines.Count == 0)
                return string.Empty;

            var sqlBuilder = new System.Text.StringBuilder();
            foreach (var line in sqlLines)
            {
                if (sqlBuilder.Length > 0)
                    sqlBuilder.Append(' ');
                sqlBuilder.Append(line);
            }

            // 使用Regex清理多余空格
            return System.Text.RegularExpressions.Regex.Replace(sqlBuilder.ToString(), @"\s+", " ").Trim();
        }

        /// <summary>
        /// 将参数替换到SQL中，支持字符串参数中的换行符处理
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数字符串，如 "@__p_0='1', @__p_1='value'"</param>
        /// <returns>替换后的SQL</returns>
        private static string ReplaceParametersInSql(string sql, string parameters)
        {
            if (string.IsNullOrEmpty(parameters) || string.IsNullOrEmpty(sql))
                return sql;

            try
            {
                // 使用正则表达式解析参数，格式如: @__p_0='1', @__p_1='value'
                var paramRegex = new System.Text.RegularExpressions.Regex(@"(@__\w+)\s*=\s*('[^']*'|\d+|NULL)", System.Text.RegularExpressions.RegexOptions.Compiled);
                var matches = paramRegex.Matches(parameters);

                // 使用StringBuilder进行高效替换
                var result = new System.Text.StringBuilder(sql);

                foreach (System.Text.RegularExpressions.Match match in matches)
                {
                    if (match.Groups.Count >= 3)
                    {
                        var paramName = match.Groups[1].Value;
                        var paramValue = match.Groups[2].Value;

                        // 处理字符串参数中的换行符，避免日志换行过多
                        if (paramValue.StartsWith("'") && paramValue.EndsWith("'"))
                        {
                            // 转义换行符为 \n
                            paramValue = paramValue.Replace("\\", "\\\\").Replace("\n", "\\n").Replace("\r", "\\r");
                        }

                        // 替换参数占位符
                        result.Replace(paramName, paramValue);
                    }
                }

                return result.ToString();
            }
            catch (Exception ex)
            {
                // 如果参数替换失败，返回原始SQL
                Console.WriteLine($"SQL参数替换失败: {ex.Message}");
                return sql;
            }
        }

        /// <summary>
        /// 判断是否为系统查询（过滤掉不重要的查询）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static bool IsSystemQuery(string sql)
        {
            if (string.IsNullOrEmpty(sql)) return true;

            var upperSql = sql.ToUpper();

            // 过滤掉这些系统查询
            return upperSql.Contains("INFORMATION_SCHEMA") ||
                    upperSql.Contains("TABLE_NAME AS NAME") ||
                    upperSql.Contains("TABLE_COMMENT AS DESCRIPTION") ||
                    upperSql.Contains("ALTER TABLE") && upperSql.Contains("COMMENT=") ||
                    upperSql.Contains("SELECT DATABASE()") ||
                    upperSql.Contains("SHOW TABLES") ||
                    upperSql.Contains("DESC ") ||
                    upperSql.Contains("DESCRIBE ");
        }

        /// <summary>
        /// 初始化数据库
        /// </summary>
        public async Task InitializeDatabaseAsync()
        {
            try
            {
                // 尝试执行数据迁移
                if ((await Database.GetPendingMigrationsAsync()).Any())
                {
                    await Database.MigrateAsync();
                }
                else
                {
                    // 如果没有待执行的迁移，确保数据库连接正常
                    await Database.EnsureCreatedAsync();
                }
            }
            catch (Exception ex)
            {
                // 如果迁移失败，可能是因为数据库已存在但结构不匹配
                // 记录错误但继续运行，让应用程序尝试使用现有数据库
                Console.WriteLine($"数据库迁移警告: {ex.Message}");
                
                // 尝试确保数据库存在
                try
                {
                    await Database.EnsureCreatedAsync();
                }
                catch
                {
                    // 忽略此错误，数据库可能已经存在
                }
            }

            // 根据配置决定是否初始化种子数据
            if (_seedDataOptions.Enabled)
            {
                await InitSeedDataAsync();
            }
            else
            {
                Console.WriteLine("种子数据初始化已禁用（配置：SeedData.Enabled = false）");
            }
        }

        /// <summary>
        /// 种子数据配置
        /// </summary>
        /// <param name="modelBuilder"></param>
        private void SeedData(ModelBuilder modelBuilder)
        {
            // 暂时注释掉种子数据，避免与现有数据冲突
            // 种子数据将在运行时通过InitSeedDataAsync方法处理
        }

        /// <summary>
        /// 初始化种子数据（运行时）
        /// </summary>
        private async Task InitSeedDataAsync()
        {
            Console.WriteLine($"开始种子数据初始化检查... (配置: Enabled={_seedDataOptions.Enabled}, CreateDefaultAdmin={_seedDataOptions.CreateDefaultAdmin})");

            if (!_seedDataOptions.CreateDefaultAdmin)
            {
                Console.WriteLine("默认管理员创建已禁用（配置：SeedData.CreateDefaultAdmin = false）");
                return;
            }

            // 检查是否已有管理员用户
            var adminUser = await Users.FirstOrDefaultAsync(u => u.UserName == _seedDataOptions.AdminUserName);
            if (adminUser == null)
            {
                Console.WriteLine($"未找到管理员用户 '{_seedDataOptions.AdminUserName}'，开始创建种子数据...");
                
                try
                {
                    // 创建管理员角色
                    var adminRole = new Role
                    {
                        RoleName = "管理员",
                        RoleCode = "admin",
                        Sort = 1,
                        Status = 1,
                        Remark = "系统管理员角色",
                        CreateTime = DateTime.Now,
                        IsDeleted = false
                    };
                    
                    Roles.Add(adminRole);
                    await SaveChangesAsync();
                    Console.WriteLine($"✓ 创建管理员角色成功，ID: {adminRole.Id}");

                    // 创建管理员用户
                    var admin = new User
                    {
                        UserName = _seedDataOptions.AdminUserName,
                        Password = BCrypt.Net.BCrypt.HashPassword(_seedDataOptions.AdminPassword),
                        RealName = _seedDataOptions.AdminRealName,
                        Email = _seedDataOptions.AdminEmail,
                        Status = 1,
                        CreateTime = DateTime.Now,
                        IsDeleted = false
                    };
                    
                    Users.Add(admin);
                    await SaveChangesAsync();
                    Console.WriteLine($"✓ 创建管理员用户成功，ID: {admin.Id}, 用户名: {admin.UserName}");

                    // 关联用户角色
                    var userRole = new UserRole
                    {
                        UserId = admin.Id,
                        RoleId = adminRole.Id,
                        CreateTime = DateTime.Now,
                        IsDeleted = false
                    };
                    
                    UserRoles.Add(userRole);
                    await SaveChangesAsync();
                    Console.WriteLine($"✓ 用户角色关联成功，UserRole ID: {userRole.Id}");

                    // 创建默认菜单数据
                    await CreateDefaultMenusAsync();
                    
                    Console.WriteLine($"🎉 种子数据初始化完成！");
                    Console.WriteLine($"   管理员用户名: {_seedDataOptions.AdminUserName}");
                    Console.WriteLine($"   管理员密码: {_seedDataOptions.AdminPassword}");
                    Console.WriteLine($"   管理员邮箱: {_seedDataOptions.AdminEmail}");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"❌ 种子数据初始化失败: {ex.Message}");
                    throw;
                }
            }
            else
            {
                Console.WriteLine($"✓ 管理员用户 '{_seedDataOptions.AdminUserName}' 已存在，跳过种子数据初始化");
            }
        }

        /// <summary>
        /// 创建默认菜单数据
        /// </summary>
        private async Task CreateDefaultMenusAsync()
        {
            // 检查是否已有菜单数据
            if (await Menus.AnyAsync())
            {
                Console.WriteLine("菜单数据已存在，跳过菜单种子数据创建");
                return;
            }

            Console.WriteLine("开始创建默认菜单数据...");

            var menus = new List<Menu>
            {
                // 系统管理
                new Menu
                {
                    Name = "系统管理",
                    Key = "system",
                    Path = "/system",
                    Icon = "SettingOutlined",
                    Type = (int)MenuType.Directory,
                    Sort = 1,
                    Status = 1,
                    CreateTime = DateTime.Now,
                    IsDeleted = false
                },
                // 用户管理
                new Menu
                {
                    Name = "用户管理",
                    Key = "system-users",
                    Path = "/system/users",
                    Icon = "UserOutlined",
                    Type = (int)MenuType.Menu,
                    Sort = 1,
                    Status = 1,
                    CreateTime = DateTime.Now,
                    IsDeleted = false
                },
                // 角色管理
                new Menu
                {
                    Name = "角色管理",
                    Key = "system-roles",
                    Path = "/system/roles",
                    Icon = "TeamOutlined",
                    Type = (int)MenuType.Menu,
                    Sort = 2,
                    Status = 1,
                    CreateTime = DateTime.Now,
                    IsDeleted = false
                },
                // 菜单管理
                new Menu
                {
                    Name = "菜单管理",
                    Key = "system-menus",
                    Path = "/system/menus",
                    Icon = "MenuOutlined",
                    Type = (int)MenuType.Menu,
                    Sort = 3,
                    Status = 1,
                    CreateTime = DateTime.Now,
                    IsDeleted = false
                }
            };

            Menus.AddRange(menus);
            await SaveChangesAsync();

            // 设置父子关系
            var systemMenu = await Menus.FirstAsync(m => m.Key == "system");
            var childMenus = await Menus.Where(m => m.Key.StartsWith("system-")).ToListAsync();
            
            foreach (var childMenu in childMenus)
            {
                childMenu.ParentId = systemMenu.Id;
            }

            await SaveChangesAsync();
            Console.WriteLine($"✓ 创建默认菜单数据成功，共 {menus.Count} 个菜单");
        }
    }
}